/**
 * <b>项目名称：</b>网络应用框架<br/>
 * <b>包    名：</b>com.hhwy.framework.configure.service<br/>
 * <b>文 件 名：</b>SystemConfigureFromDB.java<br/>
 * <b>版本信息：</b><br/>
 * <b>日    期：</b>2016年8月12日-上午10:01:16<br/>
 * <b>Copyright (c)</b> 2016恒华伟业科技股份有限公司-版权所有<br/>
 * 
 */
package com.hhwy.framework.configure.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hhwy.framework.util.CaseInsensitiveMap;

 /**
 * <b>类 名 称：</b>SystemConfigureFromDB<br/>
 * <b>类 描 述：</b><br/>
 * <b>创 建 人：</b>jlx<br/>
 * <b>修 改 人：</b>jlx<br/>
 * <b>修改时间：</b>2016年8月12日 上午10:01:16<br/>
 * <b>修改备注：</b><br/>
 * @version 1.0.0<br/>
 */
public class LanguageConfigureFromDB {

	private static Logger log = LoggerFactory.getLogger(LanguageConfigureFromDB.class);
	
	public LanguageConfigureFromDB(String driverClass,String url,String uname,String pass){
		this.driverClass=driverClass;
		this.url=url;
		this.uname=uname;
		this.password=pass;
	}
	
	private String url;
	
	private String driverClass="com.mysql.jdbc.Driver";
	
	private String uname;
	
	private String password;
	
	protected Connection conn;
	
	protected PreparedStatement psmt;
	
	
	public Map<String, String> loadConfig() throws SQLException{
		
		try {
			Map<String, String> map=new CaseInsensitiveMap<String>();
			
			//加载language_config中的读语言配置
			if (!this.existsTable("language_config")) {
				StringBuilder sb=new StringBuilder();
				
				sb.append(" CREATE TABLE `language_config` ( ");
				sb.append(" `id` varchar(32) NOT NULL, ");
				sb.append(" `language` varchar(33) NOT NULL DEFAULT '' COMMENT '可用集(中文 (中国):zh-CN,中文 (台湾地区):zh-TW,英文 (美国):en-US,英文 (英国):en-GB)', ");
				sb.append(" `pagepath` varchar(255) NOT NULL DEFAULT '' COMMENT 'jsp相对路径,不包括/view, /plugins/ .jsp, 例如：/pluginName/dir1/dir2/test', ");
				sb.append(" `key_` varchar(255) NOT NULL DEFAULT '' COMMENT '键', ");
				sb.append(" `value` varchar(255) DEFAULT '' COMMENT '值', ");
				sb.append(" `description` varchar(255) DEFAULT NULL COMMENT '描述', ");
				sb.append(" `create_time` datetime DEFAULT NULL, ");
				sb.append(" `create_user` varchar(32) DEFAULT NULL, ");
				sb.append(" `update_time` datetime DEFAULT NULL, ");
				sb.append(" `update_user` varchar(32) DEFAULT NULL, ");
				sb.append(" PRIMARY KEY (`id`) ");
				sb.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
				
				this.createTable(sb.toString());
			}
			String selectSQL = "SELECT CONCAT(REPLACE(SUBSTR(`pagepath`, 2),'/','.'),'.',`key_`,'_',`language`) AS 'key',`value` FROM language_config";
			getKeyValueFromDB(selectSQL, map);
			
			
			//加载t_code_language中的读语言配置
			if (!this.existsTable("t_code_language")) {
				StringBuilder sb=new StringBuilder();
				sb.append(" CREATE TABLE `t_code_language` ( ");
				sb.append(" `id` varchar(32) NOT NULL, ");
				sb.append("  `fun_id` varchar(255) NOT NULL, ");
				sb.append(" `language` varchar(33) DEFAULT NULL, ");
				sb.append(" `key_` varchar(255) DEFAULT NULL, ");
				sb.append("  `value` varchar(255) DEFAULT NULL, ");
				sb.append(" `description` varchar(255) DEFAULT NULL, ");
				sb.append("  `create_time` datetime DEFAULT NULL, ");
				sb.append("  `create_user` varchar(32) DEFAULT NULL, ");
				sb.append(" `update_time` datetime DEFAULT NULL, ");
				sb.append("  `update_user` varchar(32) DEFAULT NULL, ");
				sb.append("  PRIMARY KEY (`id`,`fun_id`) ");
				sb.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
				
				this.createTable(sb.toString());
			}
			
			String selectTCodeLanguageSQL = "SELECT CONCAT(`fun_id`, '.', `key_`,'_',`language`) AS 'key',`value` FROM t_code_language";
			getKeyValueFromDB(selectTCodeLanguageSQL, map);
			
			return map;
		} catch (Exception e) {
			e.printStackTrace();
			log.error("从数据库获取配置信息失败!!!");
		}
		
		throw new RuntimeException("从数据库获取配置信息为null");
	}

	private void getKeyValueFromDB(String selectSQL, Map<String, String> map)
			throws ClassNotFoundException, SQLException {
		try {
			this.open();
			
			this.psmt=this.conn.prepareStatement(selectSQL);
			
			ResultSet rset=this.psmt.executeQuery();
			
			while (rset.next()) {
				
				String key=rset.getString("key");
				
				String value=rset.getString("value");
				
				map.put(key, value);
			}
		}finally{
			this.close();
		}
	}
	
	private void createTable(String ddl)throws SQLException{
		
		try {
			
			this.open();
			
			this.psmt=this.conn.prepareStatement(ddl);
			
			this.psmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("创建表language_config失败!!!");
		}finally{
			this.close();
		}
	}
	
	private boolean existsTable(String tableName)throws SQLException{
		
		String sql="SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '"+tableName+"' and table_schema=(select database())";
		

		try {
			
			this.open();
			
			this.psmt=this.conn.prepareStatement(sql);
			
			ResultSet rset=this.psmt.executeQuery();
			
			if (rset.next()) {
				
				long count=rset.getLong(1);
				
				if (count>0) {
					return true;
				}
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("从数据库获取配置信息失败!!!");
		}finally{
			this.close();
		}
		
		return false;
	}
	

	
	private Connection open() throws ClassNotFoundException, SQLException{
		
		Class.forName(driverClass);
		
		conn=DriverManager.getConnection(this.url, this.uname, this.password);
		
		return conn;
	}
	
	private void close() throws SQLException{
		
		if (psmt!=null) {
			psmt.close();
		}
		
		if (conn!=null) {
			conn.close();
		}
	}
}
